package com.lanyou.esb.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import com.lanyou.esb.entity.Http;

/**
 * 服务响应时间查询
 * 
 * @author Davey.wu
 */
public interface IfResTimeRepository extends Repository<Http, String>,
		JpaSpecificationExecutor<Http> {

	/**
	 * 获取某一天的服务响应时间
	 * 
	 * @param ifName
	 * @param fieldId
	 * @param systemId
	 * @param targetDate
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT "
			+ "(SELECT f.FIELD_NAME FROM t_cook_db_field f WHERE f.FIELD_ID=V0.fieldId) AS fieldName, "
			+ "V0.fsName, "
			+ "V0.ifName, "
			+ "V0.avgResTime, "
			+ "V0.maxResTime, "
			+ "V0.esbAvgResTime, "
			+ "V0.esbMaxResTime "
			+ "FROM "
			+ "	(SELECT s0.FIELD_ID AS fieldId,  "
			+ "	s0.FIELD_SYSTEM_ID AS systemId,  "
			+ "	s0.SYSTEM_NAME AS fsName,  "
			+ "	t0.IF_ID AS ifId,  "
			+ "	h0.IF_NAME AS ifName, "			
			+ "	t0.AVG_TIME AS avgResTime,  "
			+ "	t0.MAX_TIME AS maxResTime,  "
			+ "	t0.ESB_AVG_TIME AS esbAvgResTime,  "
			+ "	t0.ESB_MAX_TIME AS esbMaxResTime  "
			+ "	FROM t_esb_bu_if_http h0,t_cook_bu_field_system s0, t_esb_bu_if_res_time t0  "
			+ "	WHERE t0.IF_ID=h0.IF_HTTP_ID AND h0.FIELD_SYSTEM_ID=s0.FIELD_SYSTEM_ID  "
			+ "	AND (''=:ifName OR h0.IF_NAME LIKE :ifName)  "
			+ "	AND (''=:systemId OR s0.FIELD_SYSTEM_ID =:systemId)  "
			+ "	AND (''=:fieldId OR s0.FIELD_ID=:fieldId)  "
			+ "	AND (''=:targetDate OR DATE_FORMAT(t0.STATISTICS_DATE, '%Y-%m-%d') = DATE_FORMAT(:targetDate, '%Y-%m-%d'))  "
			+ "	GROUP BY fieldId,systemId,ifId LIMIT :pageNumber,:pageSize) AS V0")
	List<Object[]> findIfResTime(@Param("ifName") String ifName,
			@Param("fieldId") String fieldId,
			@Param("systemId") String systemId,
			@Param("targetDate") String targetDate,
			@Param("pageNumber") long pageNumber,
			@Param("pageSize") long pageSize);

	/**
	 * 获取某一天的服务响应时间总记录数
	 * 
	 * @param ifName
	 * @param fieldId
	 * @param systemId
	 * @param targetDate
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT COUNT(V0.ifId) as total "
			+ "FROM "
			+ "	(SELECT s0.FIELD_ID AS fieldId,  "
			+ "	s0.FIELD_SYSTEM_ID AS systemId,  "
			+ "	t0.IF_ID AS ifId  "
			+ "	FROM t_esb_bu_if_http h0,t_cook_bu_field_system s0, t_esb_bu_if_res_time t0  "
			+ "	WHERE t0.IF_ID=h0.IF_HTTP_ID AND h0.FIELD_SYSTEM_ID=s0.FIELD_SYSTEM_ID  "
			+ "	AND (''=:ifName OR h0.IF_NAME LIKE :ifName)  "
			+ "	AND (''=:systemId OR s0.FIELD_SYSTEM_ID =:systemId)  "
			+ "	AND (''=:fieldId OR s0.FIELD_ID=:fieldId)  "
			+ "	AND (''=:targetDate OR DATE_FORMAT(t0.STATISTICS_DATE, '%Y-%m-%d') = DATE_FORMAT(:targetDate, '%Y-%m-%d'))  "
			+ "	GROUP BY fieldId,systemId,ifId) AS V0")
	List<Object> findIfResTimeTotal(@Param("ifName") String ifName,
			@Param("fieldId") String fieldId,
			@Param("systemId") String systemId,
			@Param("targetDate") String targetDate);

	/**
	 * 获取某一时间范围内的服务响应时间
	 * 
	 * @param ifName
	 * @param fieldId
	 * @param systemId
	 * @param startDate
	 * @param endDate
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT "
			+ "(SELECT f.FIELD_NAME FROM t_cook_db_field f WHERE f.FIELD_ID=V0.fieldId) AS fieldName, "
			+ "V0.fsName, "
			+ "V0.ifName, "
			+ "V0.avgResTime, "
			+ "V0.maxResTime, "
			+ "V0.esbAvgResTime, "
			+ "V0.esbMaxResTime "
			+ "FROM "
			+ "	(SELECT s0.FIELD_ID AS fieldId,  "
			+ "	s0.FIELD_SYSTEM_ID AS systemId,  "
			+ "	s0.SYSTEM_NAME AS fsName,  "
			+ "	t0.IF_ID AS ifId,  "
			+ "	h0.IF_NAME AS ifName, "
			+ "	AVG(t0.AVG_TIME) AS avgResTime,  "
			+ "	MAX(t0.MAX_TIME) AS maxResTime,  "
			+ "	AVG(t0.ESB_AVG_TIME) AS esbAvgResTime,  "
			+ "	MAX(t0.ESB_MAX_TIME) AS esbMaxResTime  "
			
			+ "	FROM t_esb_bu_if_http h0,t_cook_bu_field_system s0, t_esb_bu_if_res_time t0  "
			+ "	WHERE t0.IF_ID=h0.IF_HTTP_ID AND h0.FIELD_SYSTEM_ID=s0.FIELD_SYSTEM_ID  "
			+ "	AND (''=:ifName OR h0.IF_NAME LIKE :ifName)  "
			+ "	AND (''=:systemId OR s0.FIELD_SYSTEM_ID =:systemId)  "
			+ "	AND (''=:fieldId OR s0.FIELD_ID=:fieldId)  "
			+ "	AND (t0.STATISTICS_DATE>=:startDate AND t0.STATISTICS_DATE<=:endDate)  "
			+ "	GROUP BY fieldId,systemId,ifId LIMIT :pageNumber,:pageSize) AS V0")
	List<Object[]> findIfResTime(@Param("ifName") String ifName,
			@Param("fieldId") String fieldId,
			@Param("systemId") String systemId,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate,
			@Param("pageNumber") long pageNumber,
			@Param("pageSize") long pageSize);

	/**
	 * 获取某时间段内服务响应时间总记录数
	 * 
	 * @param ifName
	 * @param fieldId
	 * @param systemId
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT COUNT(V0.ifId) as total "
			+ "FROM "
			+ "	(SELECT s0.FIELD_ID AS fieldId,  "
			+ "	s0.FIELD_SYSTEM_ID AS systemId,  "
			+ "	t0.IF_ID AS ifId  "
			+ "	FROM t_esb_bu_if_http h0,t_cook_bu_field_system s0, t_esb_bu_if_res_time t0  "
			+ "	WHERE t0.IF_ID=h0.IF_HTTP_ID AND h0.FIELD_SYSTEM_ID=s0.FIELD_SYSTEM_ID  "
			+ "	AND (''=:ifName OR h0.IF_NAME LIKE :ifName)  "
			+ "	AND (''=:systemId OR s0.FIELD_SYSTEM_ID =:systemId)  "
			+ "	AND (''=:fieldId OR s0.FIELD_ID=:fieldId)  "
			+ "	AND (t0.STATISTICS_DATE>=:startDate AND t0.STATISTICS_DATE<=:endDate)  "
			+ "	GROUP BY fieldId,systemId,ifId) AS V0")
	List<Object> findIfResTimeTotal(@Param("ifName") String ifName,
			@Param("fieldId") String fieldId,
			@Param("systemId") String systemId,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate);

}
